CREATE OR REPLACE PACKAGE BODY DISCOUNT_USER.CJ_PRODUCT_SEARCH_PKG AS
 
PROCEDURE insertcj(
    p_adid IN DISCOUNTS_CJ.AD_ID%TYPE,
    p_advertiserid IN DISCOUNTS_CJ.ADVERTISER_ID%TYPE,
    p_advertisername IN DISCOUNTS_CJ.ADVERTISER_NAME%TYPE,
    p_advertisercategory IN DISCOUNTS_CJ.ADVERTISER_CATEGORY%TYPE,
    p_buyurl IN DISCOUNTS_CJ.BUY_URL%TYPE,
    p_catalogid IN DISCOUNTS_CJ.CATALOG_ID%TYPE,
    p_currency IN DISCOUNTS_CJ.CURRENCY%TYPE,
    p_description IN DISCOUNTS_CJ.DESCRIPTION%TYPE,
    p_imageurl IN DISCOUNTS_CJ.IMAGE_URL%TYPE,
    p_instock IN DISCOUNTS_CJ.IN_STOCK%TYPE,
    p_isbn IN DISCOUNTS_CJ.ISBN%TYPE,
    p_manufacturername IN DISCOUNTS_CJ.MANUFACTURER_NAME%TYPE,
    p_manufacturersku IN DISCOUNTS_CJ.MANUFACTURER_SKU%TYPE,
    p_name IN DISCOUNTS_CJ.NAME%TYPE,
    p_price IN DISCOUNTS_CJ.PRICE%TYPE,
    p_retailprice IN DISCOUNTS_CJ.RETAIL_PRICE%TYPE,
    p_saleprice IN DISCOUNTS_CJ.SALE_PRICE%TYPE,
    p_sku IN DISCOUNTS_CJ.SKU%TYPE,
    p_upc IN DISCOUNTS_CJ.UPC%TYPE)
    
    IS
    
    l_exists INTEGER;
    
BEGIN
    
    SELECT COUNT(*) INTO l_exists  FROM DISCOUNTS_CJ  WHERE SKU = p_sku  AND ROWNUM = 1;
   IF l_exists = 1 THEN
       
        UPDATE DISCOUNTS_CJ
        SET AD_ID=p_adid,
            ADVERTISER_ID=p_advertiserid,
            ADVERTISER_NAME=p_advertisername,
            ADVERTISER_CATEGORY=p_advertisercategory,
            BUY_URL=p_buyurl,
            CATALOG_ID=p_catalogid,
            CURRENCY=p_currency,
            DESCRIPTION=p_description,
            IMAGE_URL=p_imageurl,
            IN_STOCK=p_instock,
            ISBN=p_isbn,
            MANUFACTURER_NAME=p_manufacturername,
            MANUFACTURER_SKU=p_manufacturersku,
            NAME=p_name,
            PRICE=p_price,
            RETAIL_PRICE=p_retailprice,
            SALE_PRICE=p_saleprice,
            SKU=p_sku,
            UPC=p_upc
           
            WHERE SKU = P_sku;
 
   ELSE
    
    INSERT INTO DISCOUNTS_CJ(AD_ID,ADVERTISER_ID,ADVERTISER_NAME,ADVERTISER_CATEGORY,BUY_URL,CATALOG_ID,CURRENCY,DESCRIPTION,IMAGE_URL,IN_STOCK,ISBN,MANUFACTURER_NAME,MANUFACTURER_SKU,NAME,PRICE,RETAIL_PRICE,SALE_PRICE,SKU,UPC)
    VALUES (p_adid,
            p_advertiserid,
            p_advertisername,
            p_advertisercategory,
            p_buyurl,
            p_catalogid,
            p_currency,
            p_description,
            p_imageurl,
            p_instock,
            p_isbn,
            p_manufacturername,
            p_manufacturersku,
            p_name,
            p_price,
            p_retailprice,
            p_saleprice,
            p_sku,
            p_upc);
    
 
   END IF; 
    
END insertcj;
 
END CJ_PRODUCT_SEARCH_PKG;
/